package com.lsc.jdbc.demo.jdbc.utiltest;

import com.lsc.jdbc.demo.jdbc.util.JdbcUtils;
import com.lsc.study.enumtest.ResponseEnum;

import java.sql.*;

public class JdbcUtilsTest {

    static Connection con;
    static Statement statement;
    static ResultSet resultSet;
    static PreparedStatement preparedStatement;

    /**
     * 链接数据库的  静态代码块
     */
    static {
        con = JdbcUtils.getCon();
    }


    /**
     * 模拟sql注入问题  登录
     *
     * @param username
     * @param password
     */
    public static void login(String username, String password) {

        /* 使用 Statement 来进行 sql 语句的 编译 */
        try {
            statement = con.createStatement();
//            SELECT * FROM `login` WHERE username= 'hanamaki' AND password = '123456'
            String sql = "SELECT * FROM `login` WHERE username= '" + username + "' AND password = '" + password + "'";
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    System.out.print(resultSet.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(con, statement, resultSet, null);
        }

    }

    /**
     * 使用  PreparedStatement 来防止 sql 注入
     */
    public static void login_PreparedStatement(String username, String password) {

        /* 使用 Statement 来进行 sql 语句的 编译 */
        try {
            String sql = "SELECT * FROM `login` WHERE username= ? AND password = ?";
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            System.out.println(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    System.out.print(resultSet.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(con, statement, resultSet, null);
        }

    }


    /**
     * 测试  数据库的工具类 是否可以正常使用
     */
    public static void query() {

        /* Statement 会出现 sql  注入  */
        try {
            statement = con.createStatement();
            String sql = "select * from stu_info";
            resultSet = statement.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            System.out.println("stu_id\tstu_name\tstu_sex");
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    System.out.print(resultSet.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(con, statement, resultSet, null);
        }


    }

    /**
     * 向  数据库中 插入数据
     */
    public static void insert(String name, String sex) {

        /* 需要创建 执行数据sql 的对象 */
        try {
            String sql = "INSERT INTO `stu_info`(`stu_name`, `stu_sex`) VALUES (?, ?)";
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, sex);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println(ResponseEnum.SUCCESS);
            } else {
                System.out.println(ResponseEnum.ERROR);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }

    /**
     * 向  数据库中 修改某一条数据
     */
    public static void update(int id) {

        /* 需要创建 执行数据sql 的对象 */
        try {
            String sql = "update stu_info set stu_sex = '女' where stu_id = ?";
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println(ResponseEnum.SUCCESS);
            } else {
                System.out.println(ResponseEnum.ERROR);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }

    /**
     * 删除一条记录
     */
    public static void delete(int id) {

        /* 需要创建 执行数据sql 的对象 */
        try {
            String sql = "delete from stu_info where stu_id = ?";
            preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println(ResponseEnum.SUCCESS);
            } else {
                System.out.println(ResponseEnum.ERROR);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }


    /**
     * 模拟转账 操作  你 ---- 》 我    money
     */
    public static void transaction(int transferPersonId, int payeeId, int money) {

        /**
         * 如果想要在 Java中使用 事务  首先需要将事务的自动提价给关闭
         */
        try {
            con.setAutoCommit(false);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            /* 1. 转账人的 操作 */
            String transferPersonSql = "update money set money = money - ? where zh_id = ?";
            PreparedStatement p1 = con.prepareStatement(transferPersonSql);
            p1.setInt(1, money);
            p1.setInt(2, transferPersonId);
            p1.executeUpdate();

            /* 2。收账人的操作  */
            String payeeSql = "update money set money = mney + ? where zh_id = ?";
            PreparedStatement p2 = con.prepareStatement(payeeSql);
            p2.setInt(1, money);
            p2.setInt(2, payeeId);
            p2.executeUpdate();

            /* 如果在 try 监控区域中 就是成功的  不然就是失败的  */
            con.commit();
            System.out.println("成功");
        } catch (SQLException throwables) {
            try {
                con.rollback();
                System.out.println("失败");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }


    public static void main(String[] args) {

        query();
//        login("hanamaki", "123456");
//        login(" ' or '1=1 ", " ' or '1=1 ");

//        login_PreparedStatement("hanamaki", "123456");
//        login_PreparedStatement(" ' or '1=1 ", " ' or '1=1 ");

//        insert("hello", "男");
//        update(5);
//        delete(5);

//        transaction(1, 2, 500);

    }


}
